﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core.Enums;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using Kingdee.K3.FIN.AP.App.Report;
using Kingdee.K3.FIN.App.Core.ARAP.AbstractReport;
using Kingdee.K3.FIN.AR.App.Report;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LiMinCRMPlugin
{
    /// <summary>
    /// 应收款明细表二开
    /// </summary>
    /// <remarks>
    /// 功能：扩展增加销售员字段。
    /// 创建人：wzx
    /// 创建日期：2024.7.18
    /// 修改信息（1）：修改人，修改日期，修改内容
    /// </remarks>
    [Description("应收款明细表二开")]
    [HotUpdate]
    public class AccountsReceivableDetailsDiy: ARDetailReportService
    {
        private string[] TempTableName;

        //重写创建临时表方法

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            //创建临时表用于保存自己的数据
            IDBService dbservice = Kingdee.BOS.App.ServiceHelper.GetService<IDBService>();
            TempTableName = dbservice.CreateTemporaryTableName(this.Context, 1);
            string strTable = TempTableName[0];

            //调用基类方法，获取初步的查询结果
            base.BuilderReportSqlAndTempTable(filter, strTable);

            //对初步的查询结果处理，然后回填到默认的存放查询结果到临时表 
            StringBuilder sb = new StringBuilder();

            string strSql = @"/*dialect*/select t1.*,
        case when t1.FFORMID='AR_receivable'
        then (select FNAME from T_HR_EMPINFO_L where FID=t2.F_DEV_Saler and FLOCALEID=2052)
        when t1.FFORMID='AR_RECEIVEBILL'
        then (select FNAME from T_HR_EMPINFO_L where FID=t3.F_DEV_Saler and FLOCALEID=2052)
        when t1.FFORMID='AR_REFUNDBILL'
        then (select FNAME from T_HR_EMPINFO_L where FID=t4.F_DEV_Saler and FLOCALEID=2052)
        else ''
        end as F_QLTK_SalespersonName,
        case when t1.FFORMID='AR_receivable'
        then (select FNumber from T_HR_EMPINFO where FID=t2.F_DEV_Saler) 
        when t1.FFORMID='AR_RECEIVEBILL'
        then (select FNumber from T_HR_EMPINFO where FID=t3.F_DEV_Saler) 
        when t1.FFORMID='AR_REFUNDBILL'
        then (select FNumber from T_HR_EMPINFO where FID=t4.F_DEV_Saler) 
        else ''
        end as F_QLTK_SalespersonNo,

        case when t1.FFORMID='AR_receivable'
        then (select FNAME from RDLV_t_sale_body_L where FID=t2.F_RDLV_saleBody and FLOCALEID=2052)
        when t1.FFORMID='AR_RECEIVEBILL'
        then (select FNAME from RDLV_t_sale_body_L where FID=t3.F_QLTK_saler and FLOCALEID=2052)
        when t1.FFORMID='AR_REFUNDBILL'
        then (select FNAME from RDLV_t_sale_body_L where FID=t4.F_QLTK_saler and FLOCALEID=2052)
        else ''
        end as F_QLTK_saleBody,
        case when t1.FCONTACTUNITTYPE='BD_Customer'
        then (SELECT B.FNAME FROM T_BD_CUSTOMER as A
        left join T_BD_DEPARTMENT_L AS B  on A.FSALDEPTID=B.FDEPTID AND B.FLOCALEID=2052 
        where FCUSTID=t1.FCONTACTUNITID)

        else ''
        end as F_QLTK_bm3,
        case when t1.FFORMID='AR_receivable'
        then (SELECT FCONVERTDENOMINATOR/FCONVERTNUMERATOR FROM T_BD_UNITCONVERTRATE AS A
        LEFT JOIN T_BD_UNIT_L  AS B ON A.FDESTUNITID=B.FUNITID AND B.FLOCALEID=2052
        WHERE A.FCURRENTUNITID = '130900' AND A.FMATERIALID = t1.FMaterialID AND B.FNAME = t1.funit )
        when t1.FFORMID='AR_RECEIVEBILL'
        then (SELECT FCONVERTDENOMINATOR/FCONVERTNUMERATOR FROM T_BD_UNITCONVERTRATE AS A
        LEFT JOIN T_BD_UNIT_L  AS B ON A.FDESTUNITID=B.FUNITID AND B.FLOCALEID=2052
        WHERE A.FCURRENTUNITID = '130900' AND A.FMATERIALID = t1.FMaterialID AND B.FNAME = t1.funit)
        when t1.FFORMID='AR_REFUNDBILL'
        then (SELECT FCONVERTDENOMINATOR/FCONVERTNUMERATOR FROM T_BD_UNITCONVERTRATE AS A
        LEFT JOIN T_BD_UNIT_L  AS B ON A.FDESTUNITID=B.FUNITID AND B.FLOCALEID=2052
        WHERE A.FCURRENTUNITID = '130900' AND A.FMATERIALID = t1.FMaterialID AND B.FNAME = t1.funit)
        else 0
        end * FQty as FQtyJS
                                        into {0}  from  {1} t1 left join  t_AR_receivable  t2 
                                    on t1.FID = t2.FID and t1.FFORMID='AR_receivable' 
                                    left join  T_AR_RECEIVEBILL  t3 on t1.FID = t3.FID and t1.FFORMID='AR_RECEIVEBILL'
                                    left join  T_AR_REFUNDBILL  t4 on t1.FID = t4.FID and t1.FFORMID='AR_REFUNDBILL'";


            sb.AppendFormat(strSql, tableName, strTable);

            DBUtils.Execute(this.Context, sb.ToString());

        }

       

        //重写创建临时表方法
        //        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        //        {
        //            //获取过滤条件
        //            DynamicObject customFilter = filter.FilterParameter.CustomFilter as DynamicObject;
        //            string FShowByMaterial = customFilter["FShowByMaterial"].ToString();//是否按物料明细显示

        //            //创建临时表用于保存自己的数据
        //            IDBService dbservice = Kingdee.BOS.App.ServiceHelper.GetService<IDBService>();
        //            TempTableName = dbservice.CreateTemporaryTableName(this.Context, 2);
        //            string strTable = TempTableName[0];
        //            string strTable1 = TempTableName[1];

        //            string str = "";

        //            str = filter.FilterParameter.FilterString;
        //            filter.FilterParameter.FilterString = "";

        //            //调用基类方法，获取初步的查询结果
        //            base.BuilderReportSqlAndTempTable(filter, strTable);

        //            //对初步的查询结果处理，然后回填到默认的存放查询结果到临时表
        //            StringBuilder sb = new StringBuilder();
        //            StringBuilder sb1 = new StringBuilder();

        //            //未扩展之前,数据放在t1 strTable 应收款明细表
        //            //关联应收单 left join  t_AR_receivable  t2 ,单据编号 FBILLNO
        //            //再关联员工表 left join T_BD_CUSTOMER_L t3 中文 FLOCALEID=2052
        //            //left join T_BD_CUSTOMER t4
        //            //转换字段 as F_QLTK_SalespersonName，  as F_QLTK_SalespersonNo
        //            if (FShowByMaterial == "True")
        //            {
        //                string strSql = @"/*dialect*/select t1.*,
        //case when t1.FFORMID='AR_receivable'
        //then (select FNAME from T_HR_EMPINFO_L where FID=t2.F_DEV_Saler and FLOCALEID=2052)
        //when t1.FFORMID='AR_RECEIVEBILL'
        //then (select FNAME from T_HR_EMPINFO_L where FID=t3.F_DEV_Saler and FLOCALEID=2052)
        //when t1.FFORMID='AR_REFUNDBILL'
        //then (select FNAME from T_HR_EMPINFO_L where FID=t4.F_DEV_Saler and FLOCALEID=2052)
        //else ''
        //end as F_QLTK_SalespersonName,
        //case when t1.FFORMID='AR_receivable'
        //then (select FNumber from T_HR_EMPINFO where FID=t2.F_DEV_Saler) 
        //when t1.FFORMID='AR_RECEIVEBILL'
        //then (select FNumber from T_HR_EMPINFO where FID=t3.F_DEV_Saler) 
        //when t1.FFORMID='AR_REFUNDBILL'
        //then (select FNumber from T_HR_EMPINFO where FID=t4.F_DEV_Saler) 
        //else ''
        //end as F_QLTK_SalespersonNo,

        //case when t1.FFORMID='AR_receivable'
        //then (select FNAME from RDLV_t_sale_body_L where FID=t2.F_RDLV_saleBody and FLOCALEID=2052)
        //when t1.FFORMID='AR_RECEIVEBILL'
        //then (select FNAME from RDLV_t_sale_body_L where FID=t3.F_QLTK_saler and FLOCALEID=2052)
        //when t1.FFORMID='AR_REFUNDBILL'
        //then (select FNAME from RDLV_t_sale_body_L where FID=t4.F_QLTK_saler and FLOCALEID=2052)
        //else ''
        //end as F_QLTK_saleBody,
        //case when t1.FCONTACTUNITTYPE='BD_Customer'
        //then (SELECT B.FNAME FROM T_BD_CUSTOMER as A
        //left join T_BD_DEPARTMENT_L AS B  on A.FSALDEPTID=B.FDEPTID AND B.FLOCALEID=2052 
        //where FCUSTID=t1.FCONTACTUNITID)

        //else ''
        //end as F_QLTK_bm3,
        //case when t1.FFORMID='AR_receivable'
        //then (SELECT FCONVERTDENOMINATOR/FCONVERTNUMERATOR FROM T_BD_UNITCONVERTRATE AS A
        //LEFT JOIN T_BD_UNIT_L  AS B ON A.FDESTUNITID=B.FUNITID AND B.FLOCALEID=2052
        //WHERE A.FCURRENTUNITID = '130900' AND A.FMATERIALID = t1.FMaterialID AND B.FNAME = t1.funit )
        //when t1.FFORMID='AR_RECEIVEBILL'
        //then (SELECT FCONVERTDENOMINATOR/FCONVERTNUMERATOR FROM T_BD_UNITCONVERTRATE AS A
        //LEFT JOIN T_BD_UNIT_L  AS B ON A.FDESTUNITID=B.FUNITID AND B.FLOCALEID=2052
        //WHERE A.FCURRENTUNITID = '130900' AND A.FMATERIALID = t1.FMaterialID AND B.FNAME = t1.funit)
        //when t1.FFORMID='AR_REFUNDBILL'
        //then (SELECT FCONVERTDENOMINATOR/FCONVERTNUMERATOR FROM T_BD_UNITCONVERTRATE AS A
        //LEFT JOIN T_BD_UNIT_L  AS B ON A.FDESTUNITID=B.FUNITID AND B.FLOCALEID=2052
        //WHERE A.FCURRENTUNITID = '130900' AND A.FMATERIALID = t1.FMaterialID AND B.FNAME = t1.funit)
        //else 0
        //end * FQty as FQtyJS
        //                                into {0}  from  {1} t1 left join  t_AR_receivable  t2 
        //                            on t1.FID = t2.FID and t1.FFORMID='AR_receivable' 
        //                            left join  T_AR_RECEIVEBILL  t3 on t1.FID = t3.FID and t1.FFORMID='AR_RECEIVEBILL'
        //                            left join  T_AR_REFUNDBILL  t4 on t1.FID = t4.FID and t1.FFORMID='AR_REFUNDBILL'";
        //                sb.AppendFormat(strSql, strTable1, strTable);
        //                DBUtils.Execute(this.Context, sb.ToString());
        //                if (str != null && str != "")
        //                {
        //                    string strSql1 = @"/*dialect*/select *,
        //                            CASE 
        //                                WHEN (FQtyJS+FDISCOUNTAMOUNTFOR) = 0 THEN NULL 
        //                                ELSE ROUND((FAMOUNT+FDISCOUNTAMOUNTFOR)/FQtyJS,2) 
        //                              END AS FAmountJ   
        //                             into {0}  from  {1} WHERE {2}";
        //                    sb1.AppendFormat(strSql1, tableName, strTable1, str);
        //                }
        //                else 
        //                {
        //                    string strSql1 = @"/*dialect*/select *,
        //                            CASE 
        //                                WHEN (FQtyJS+FDISCOUNTAMOUNTFOR) = 0 THEN NULL 
        //                                ELSE ROUND((FAMOUNT+FDISCOUNTAMOUNTFOR)/FQtyJS,2) 
        //                              END AS FAmountJ    
        //                             into {0}  from  {1}";
        //                    sb1.AppendFormat(strSql1, tableName, strTable1);
        //                }



        //                DBUtils.Execute(this.Context, sb1.ToString());
        //            }
        //            else
        //            {
        //                string strSql = @"/*dialect*/select t1.*,
        //case when t1.FFORMID='AR_receivable'
        //then (select FNAME from T_HR_EMPINFO_L where FID=t2.F_DEV_Saler and FLOCALEID=2052)
        //when t1.FFORMID='AR_RECEIVEBILL'
        //then (select FNAME from T_HR_EMPINFO_L where FID=t3.F_DEV_Saler and FLOCALEID=2052)
        //when t1.FFORMID='AR_REFUNDBILL'
        //then (select FNAME from T_HR_EMPINFO_L where FID=t4.F_DEV_Saler and FLOCALEID=2052)
        //else ''
        //end as F_QLTK_SalespersonName,
        //case when t1.FFORMID='AR_receivable'
        //then (select FNumber from T_HR_EMPINFO where FID=t2.F_DEV_Saler) 
        //when t1.FFORMID='AR_RECEIVEBILL'
        //then (select FNumber from T_HR_EMPINFO where FID=t3.F_DEV_Saler) 
        //when t1.FFORMID='AR_REFUNDBILL'
        //then (select FNumber from T_HR_EMPINFO where FID=t4.F_DEV_Saler) 
        //else ''
        //end as F_QLTK_SalespersonNo,

        //case when t1.FFORMID='AR_receivable'
        //then (select FNAME from RDLV_t_sale_body_L where FID=t2.F_RDLV_saleBody and FLOCALEID=2052)
        //when t1.FFORMID='AR_RECEIVEBILL'
        //then (select FNAME from RDLV_t_sale_body_L where FID=t3.F_QLTK_saler and FLOCALEID=2052)
        //when t1.FFORMID='AR_REFUNDBILL'
        //then (select FNAME from RDLV_t_sale_body_L where FID=t4.F_QLTK_saler and FLOCALEID=2052)
        //else ''
        //end as F_QLTK_saleBody,
        //case when t1.FCONTACTUNITTYPE='BD_Customer'
        //then (SELECT B.FNAME FROM T_BD_CUSTOMER as A
        //left join T_BD_DEPARTMENT_L AS B  on A.FSALDEPTID=B.FDEPTID AND B.FLOCALEID=2052 
        //where FCUSTID=t1.FCONTACTUNITID)
        //else ''
        //end as F_QLTK_bm3
        //                                into {0}  from  {1} t1 left join  t_AR_receivable  t2 
        //                            on t1.FID = t2.FID and t1.FFORMID='AR_receivable' 
        //                            left join  T_AR_RECEIVEBILL  t3 on t1.FID = t3.FID and t1.FFORMID='AR_RECEIVEBILL'
        //                            left join  T_AR_REFUNDBILL  t4 on t1.FID = t4.FID and t1.FFORMID='AR_REFUNDBILL'";
        //                sb.AppendFormat(strSql, strTable1, strTable);
        //                DBUtils.Execute(this.Context, sb.ToString());
        //                if (str != null && str != "")
        //                {
        //                    string strSql1 = @"/*dialect*/select *
        //                             into {0}  from  {1} WHERE {2}";
        //                    sb1.AppendFormat(strSql1, tableName, strTable1, str);
        //                }
        //                else 
        //                {
        //                    string strSql1 = @"/*dialect*/select *
        //                             into {0}  from  {1} ";
        //                    sb1.AppendFormat(strSql1, tableName, strTable1);
        //                }

        //                DBUtils.Execute(this.Context, sb1.ToString());
        //            }
        //            filter.FilterParameter.FilterString = str;

        //        }

        public override void CloseReport()
        {
            //删除临时表
            if (TempTableName == null || TempTableName.Length == 0)
            {
                return;
            }
            IDBService dbservice = Kingdee.BOS.App.ServiceHelper.GetService<IDBService>();
            dbservice.DeleteTemporaryTableName(this.Context, TempTableName);
            base.CloseReport();
        }

     
    }
}
